/*
Purpose: Create Figure 1 of paper (NAEP gains from 1990/1992 to 2019 and then losses to 2022)
*/

global mean92 266.869669674271 //source of this is raw/state_scale_score, national public 1992 mean
global sd92 36.3153155648024

* // data set with 2022 scores
import excel using "data/raw/math8_2022.xls", first clear cellrange(A9:D62)
ren Jurisdiction state_name
ren Averagescalescore scale_score
ren Year year
keep state_name scale_score year
statastates, name(state_name)
drop if state_fips==.
drop state_name state_fips _merge
gen scale_score_norm = (scale_score - $mean92) / $sd92
tempfile naep2022
save `naep2022', replace

use "data/clean/math8_normed", clear
append using `naep2022'

keep if year==1990|year==1992|year==2019|year==2022
drop if state_fips==0
keep scale_score_norm state_abbrev year

reshape wide scale_score_norm, i(state_abbrev) j(year)
gen scale_score_base = scale_score_norm1990
replace scale_score_base = scale_score_norm1992 if scale_score_base==.

gen diff_90_19 = scale_score_norm2019 - scale_score_base
gen diff_19_22 = scale_score_norm2022 - scale_score_norm2019
drop if diff_90_19 == .

gsort -diff_90_19
gen states_sorted = _n
labmask states_sorted, values(state_abbrev)

gen stack1 = diff_90_19 + diff_19_22
gen stack2 = diff_19_22 if diff_90_19 > 0
replace stack2 = stack1 if diff_90_19 < 0
gen zero = 0
gen stack3 = 0 if diff_90_19 >= 0
replace stack3 = diff_90_19 if diff_90_19 < 0
gen labpos = diff_90_19
replace labpos = 0 if diff_90_19 < 0
drop if state_abbrev=="DC"

twoway (rbar zero diff_90_19 states_sorted, color(red) barwidth(.75)) (scatter stack1 states_sorted, mcolor(black) msymbol(arrow) msangle(180)) (rspike stack1 diff_90_19 states_sorted, lcolor(black)) (scatter labpos states_sorted, msymbol(none) mlabel(states_sorted) mlabcolor(black) mlabsize(tiny) mlabposition(12)), legend(order(1 "Change 1990-2019 (Pre-Pandemic)" 2 "Change 2019-2022") position(6)) xlabel(none) xtitle("") ytitle("In 1992 Standard Deviations") ylabel(-0.2(0.2)1) saving("results/figure1.gph", replace)